This guide to enabling middle-grade mathematics teachers to use Microsoft Excel in the classroom focuses primarily on concepts taught in grades 4–10, with some pre-algebra lessons for higher grades. Including files available for downloading to create custom math worksheets, build self-grading interactive spreadsheets, and use Excel to demonstrate charts and Cartesian math, this handbook provides fun examples of probability, solving equations, magic squares, and compound interest.

Adrienne Soucy

P.K. Hari

Calculation Basics

Excel is great at doing math. When Dan Bricklin conceived of the first spreadsheet in 1978, he envisioned a calculator where you could set up a math problem, but then scroll backwards in time and change the terms in the problem to see a new answer. Along with Bob Frankston, he developed VisiCalc – a Visible Calculator. Since VisiCalc in 1979, all spreadsheets have been able to calculate.

This section will teach you the basic math operators and the functions available for demonstrating classroom math. There are at least three common methods of entering formulas. In the first three examples below, you will learn these three methods of entering formulas. You can then choose whichever method is the easiest for you.

Using Parentheses to Control the Order of Operations

When it comes to math operating signs in an Excel formula, Excel understands
and uses the order of operations correctly. It does not necessarily move from
left to right within the formula. Instead, it follows the old mnemonic phrase
“Please Excuse My Dear Aunt Sally”: Parentheses (P) and other grouping
symbols take precedence, followed by Exponents (E). Next, Multiplication (M)
and Division (D) are done in order from left to right, and then Addition (A) and
Subtraction (S), also from left to right. The only exception is that if you use a
minus sign in front of a number, Excel assumes it to be a negative number first
before performing any other operation. If your students understand how to
calculate correctly using the order of operations, then they should have few
problems writing formulas.

A very simple word problem can explain how this works.

Jimmy brought 4 candy bars to the club house.

Calvin brought 2 candy bars.

Suzy brought 6 candy bars.

They agreed to split the candy bars equally. How many candy bars does
each club member get?

The obvious solution is to add the numbers together and divide by 3. However,
using the formula =4+2+6/3 results in the wrong answer of 8. To fix this, you
need to enclose the addition in parenthesis so that Excel knows to do the
adding first, followed by division. The formula =(4+2+6)/3 gives the correct
result of 4.

 

1. Math facts work sheets 2. Math facts on the computer that self-grade 3. Charts and graphs 4. Coordinate Grid 5. Probability (done on the computer) a. coin toss b. toss a die c. tossing 2 dice 6. Guess and check worksheets (done on the computer) a. maximize area with a given perimeter b. maximize rectangular volume c. minimize material needed to package given volume 7. Factoring Polynomials 8. Matrix computation 9. Solving two equations with two unknowns. 10. Magic Squares 11. Exploring simple and compound interest 12. Tessellations (using the draw commands to build tiled patterns)

Bill Jelen is the host of MrExcel.com. You can find him on TechTV in Canada and Australia, on his daily video Excel podcast or doing a seminar for your local teacher's association. He is the co-author of 15 books about Excel, including Excel for Teachers, Pivot Table Data Crunching, and Special Edition Using Excel 2007.